package com.supermap.dataservice.cloud.productC.specialty;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.logging.log4j.util.Strings;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @desc
 * @date 2019年04月11 11:10
 * @email 1301584033@qq.com
 */
@Mapper
public interface SpecialtyMapper {

    class  SpecialtyMapperSQL{
        public String getSpecialtyGropsql(Map<String,Object> map){
            Integer userId = (Integer) map.get("userId");
            String keyName = (String) map.get("keyName");
            StringBuffer stringBuffer = new StringBuffer();
            if(keyName!=null){
                stringBuffer.append("      select * from ( ");
            }
            stringBuffer.append("select t3.id,t3.modename,t2.pngurl,t3.make_unit\n" +
                    "              from t_farm_product t1\n" +
                    "             inner join t_area_product t2\n" +
                    "             on t1.qid=t2.id\n" +
                    "             inner join t_mode_info t3\n" +
                    "             on t2.`mid`=t3.id  where t1.userid="+userId+" group by t3.modename ");
            if(keyName!=null){
                stringBuffer.append("   )tt1 where tt1.modename like concat(\"%\",'"+keyName+"',\"%\")  or tt1.make_unit like concat(\"%\",'"+keyName+"',\"%\") ;\n" +
                        "              ");
            }
            return  stringBuffer.toString();
        }

        public String getSpecialtyCatalogByAreaCodeSQL (Map map){

            String areaCode=(String)map.get("areaCode");
            String sdate =(String)map.get("sdate");
            String edate =(String)map.get("edate");

            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("SELECT t.areacode , t.areaname , t.modename,t.createtime\n" +
                    "FROM \n" +
                    " (\n" +
                    "SELECT t2.id,t1.areacode,a.areaname ,t2.modename, DATE_FORMAT(t1.createtime,'%Y-%m-%d %H:%i:%S') AS createtime\n" +
                    "FROM t_area_product t1\n" +
                    "INNER JOIN t_mode_info t2 ON t1.mid = t2.id\n" +
                    "inner join t_area a on a.areacode = t1.areacode\n" +
                    "WHERE t1.areacode LIKE concat('"+areaCode+"','%')\n") ;
            if(sdate!=null && edate!=null){
                stringBuilder.append(" and t1.createtime between '"+sdate+"' and '"+edate+" 23:59:59'");
            }
            stringBuilder.append("ORDER BY t1.createtime DESC) t\n" +
                    "GROUP BY t.areacode, t.modename \n" +
                    "ORDER BY t.createtime DESC");

            return  stringBuilder.toString();
        }
    }

    @SelectProvider(type=SpecialtyMapperSQL.class,method = "getSpecialtyGropsql")
    List<Map<String,Object>> getSpecialtyGrop(@Param("userId")Integer userId, @Param("keyName")String keyName);


    @SelectProvider(type=SpecialtyMapperSQL.class,method = "getSpecialtyCatalogByAreaCodeSQL")
    List<Map<String,Object>> getSpecialtyCatalogByAreaCode(@Param("areaCode") String areaCode, @Param("sdate")String sdate, @Param("edate")String edate);

    /**
     * 指定获取该产品组下面的产品ff808081673e363101673fa3a93b0002
     * */
    @Select("\n" +
            "\t\tselect * from\n" +
                "\t\t(select t5.name subjectName,t4.name typeName, t3.showName,t1.title,t1.info,date_format(t1.stime,'%Y-%m-%d %H:%i:%S') as stime ,'JianYi' state,'' jsonurl \n" +
                "\t\tfrom t_suggest t1\n" +
                "\t\tinner join t_suggest_farm t2 on t1.id=t2.suggestId\n" +
                "\t\tinner join t_user t3 on t1.userid = t3.id\n" +
                "\t\tinner join t_suggest_type t4 on t1.typeid= t4.id\n" +
                "\t\tinner join t_subject t5 on t1.subjectid= t5.code\n" +
                "\t\twhere t2.farm =#{farmId}\n" +
            "\t\tunion\n" +
                "\t\t select '' subjectName,'' typeName,'' showName, REPLACE(t3.modename,'适宜度模型','') title,t1.content info,date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') stime,'ZhuanYe' state,t2.jsonurl \n" +
                "\t\tfrom t_farm_product t1 \n" +
                "\t\tinner join  t_area_product t2 on t1.qid=t2.id\n" +
                "\t\tinner join t_mode_info t3  on t2.`mid`=t3.id\n" +
                "\t\twhere t1.farm =#{farmId}\n" +
                "\t\tgroup by t1.createtime ) t\n" +
            "\t\torder by t.stime desc limit  #{pageSize}\n" +
            " \n")
    List<Map<String,Object>> getProductSpecialtyList(@Param("farmId")Integer farmId, @Param("pageSize")Integer pageSize);

    @Select("\n" +
            "\t  \n" +
            "\t\t select t1.id,  t3.jsonurl,t3.pngurl,t3.areacode,\n" +
            "\t\t REPLACE(t1.modename,\"适宜度模型\",\"\") as title,t4.content as info,date_format(t4.createtime,'%Y-%m-%d %H:%i:%S')  as ctime \n" +
            "\t\t from t_mode_info t1 \n" +
            "\t\t inner join t_mode_info t2 on t1.modename = t2.modename \n" +
            "\t\t inner join t_area_product t3 on t3.mid = t1.id \n" +
            "\t\t inner join t_farm_product t4 on t3.id = t4.qid \n" +
            "\t\t where t4.userid=#{userId} and t2.id=#{groupId} group by t1.createtime order by t1.createtime desc  limit #{pageNum}, #{pageSize}\n" +
            "\n" +
            " \n")
    List<Map<String,Object>> getSpecialtyByUserIdList(@Param("userId")String userId, @Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize, @Param("groupId")Integer groupId);
    @Select("\n" +
            "\t  \n" +
            "\t\t select count(1)\n" +
            "\t\t  from t_farm_product t1\n" +
            "\t\t inner join t_area_product t2\n" +
            "\t\t on t1.qid=t2.id\n" +
            "\t\t inner join t_mode_info t3\n" +
            "\t\t on t2.`mid`=t3.id where t1.userid=#{userId} and t3.id=#{groupId} \n" +
            "\n" +
            " \n")
    Integer getSpecialtyByUserIdListCountSum(@Param("userId")String userId, @Param("groupId")Integer groupId);


    /**
     * \获取用户收到的专业服务产品
     * */
    @Select("\tSELECT distinct(DATE(t.createtime)) ctime,t.jsonurl, t.pngurl\n" +
            "FROM t_area_product t\n" +
            "INNER JOIN t_farm_product t1 ON t.id = t1.qid\n" +
            "WHERE t.mid IN (\n" +
            "SELECT id\n" +
            "FROM t_mode_info\n" +
            "WHERE modename = (\n" +
            "SELECT modename\n" +
            "FROM t_mode_info t\n" +
            "WHERE t.id = #{groupId}\n" +
            ")) AND t.areacode = #{areacode} AND t1.userid =#{userId}\n" +
            "GROUP BY t.id\n" +
            "ORDER BY t.createtime DESC \n"
    )
    List<Map<String,Object>> getSpecialtyByUserIdListJsonL(@Param("userId")String userId, @Param("areacode")String areacode, @Param("groupId")Integer groupId);

/*

    @Select("SELECT t.areacode , t.areaname , t.modename,t.createtime\n" +
            "FROM \n" +
            " (\n" +
            "SELECT t2.id,t1.areacode,a.areaname ,t2.modename, DATE_FORMAT(t1.createtime,'%Y-%m-%d %H:%i:%S') AS createtime\n" +
            "FROM t_area_product t1\n" +
            "INNER JOIN t_mode_info t2 ON t1.mid = t2.id\n" +
            "inner join t_area a on a.areacode = t1.areacode\n" +
            "WHERE t1.areacode LIKE CONCAT(#{areaCode},\"%\")\n" +
            "ORDER BY t1.createtime DESC) t\n" +
            "GROUP BY t.areacode, t.modename \n" +
            "ORDER BY t.createtime DESC"
    )
    List<Map<String,Object>> getSpecialtyCatalogByAreaCode(@Param("areaCode") String areaCode);
*/

    @Select("select  date(t1.createtime) as createtime , t1.content ,t1.pngurl  from  t_area_product t1 inner join t_mode_info t2 on t1.mid = t2.id\n" +
            "where t1.areacode = #{areaCode} and  t2.modename =#{modeName}  order by  t1.createtime desc")
    List<Map<String,Object>> getSpecialtyImgListByCodeAndModeName(@Param("areaCode") String areaCode , @Param("modeName") String modeName);


}
